<!DOCTYPE html>
<head>
    <title>Campeonato de Futbol</title>
</head>

<style type="text/css"> 
    body{ 
        background-image: url(fondo.jpg);
        background-size: cover;
        -moz-background-size: cover;
        -webkit-background-size: cover;
        -o-background-size: cover;
        background-position : center; 
        background-repeat : no-repeat; 
        background-attachment : fixed; 
    } 
</style> 
<body>
<option id="hm" value="home.php"></option>
<input type="button" onclick="document.location.href=document.getElementById('hm').value" value="Home">
</body>

<center>
<h1>Listado de jugadores con cantidad de tarjetas</h1>
</center>

<?php

$db = pg_connect("host=localhost dbname=boti user=no-jodas password=1234");

$query = "SELECT nombre_del_empleado, 
          sum(case when es.tipo_de_la_estadistica = 'tarjeta_roja' then 1 else 0 end) AS rojas,
          sum(case when es.tipo_de_la_estadistica = 'tarjeta_amarilla' then 1 else 0 end) AS amarillas
          FROM (estadistica 
          natural JOIN empleados) AS es 
          WHERE es.tipo_de_la_estadistica='tarjeta_roja' OR es.tipo_de_la_estadistica='tarjeta_amarilla' 
          GROUP BY nombre_del_empleado
          ORDER BY 2 DESC, 3 DESC";

$result = pg_query($query);
$rows = pg_numrows($result);

echo '<center>';
echo '<table bgcolor=#A9E2F3 border = 1>';
echo "\t\t<td><h2><center>Jugador</center></h2></td>\n";
echo "\t\t<td><h2><center>Tarjetas Rojas</center></h2></td>\n";
echo "\t\t<td><h2><center>Tarjetas Amarillas</center></h2></td>\n";

for($i=1; $i<=$rows; $i++){

    $line = pg_fetch_array($result, null, PGSQL_ASSOC);
    echo '<tr>'; 
    echo "\t\t<td><h3><center>$line[nombre_del_empleado]</center></h3></td>\n";
    echo "\t\t<td><h3><center>$line[rojas]</center></h3></td>\n";
    echo "\t\t<td><h3><center>$line[amarillas]</center></h3></td>\n";
    echo '</tr>';

}
echo '</table>';
echo '</center>';

?>

          
